Course: MySQL Training Course
Topic: Understanding Trigger Execution and Limitations
Course Link: MySQL Training Course at JustAcademy
Demo Link: Register for a Course Demo
General Website: JustAcademy
A MySQL trigger is a database object associated with a table that executes automatically when a specified event occurs. Triggers are commonly used to validate data, maintain audit records, track historical changes, and apply business rules when rows are inserted, updated, or deleted.
Understanding trigger execution means knowing when a trigger runs, how often it runs, which row values it can access, how it interacts with transactions, and which SQL operations are restricted inside its body.
Although triggers can automate important database tasks, they must be designed carefully. A trigger may affect the performance and outcome of an SQL statement without being explicitly visible in the application's query.
After studying this topic, you should be able to:
A trigger is a named database object associated with a table. MySQL automatically activates it when a supported table operation occurs. Unlike a stored procedure, a trigger is not normally called directly using CALL; it runs because its associated event takes place.
| Event | BEFORE Trigger | AFTER Trigger |
|---|---|---|
| INSERT | Runs before the row is inserted. | Runs after the row is inserted successfully. |
| UPDATE | Runs before the row is updated. | Runs after the row is updated successfully. |
| DELETE | Runs before the row is deleted. | Runs after the row is deleted successfully. |
MySQL triggers are row-level triggers. Each trigger is associated with one table, one event, and one timing.
When an SQL statement changes rows in a table, MySQL processes the statement and invokes applicable triggers at the appropriate point in the row operation. The following steps provide a simplified conceptual overview.
The exact internal processing includes additional checks and can vary with the SQL statement. The important point is that trigger execution is part of the triggering database operation, not an independent job that runs separately.
Suppose an employee's salary changes from 40000 to 45000. A BEFORE UPDATE trigger can validate the proposed salary, and an AFTER UPDATE trigger can record the previous and new salary in an audit table.
UPDATE employees
SET salary = 45000.00
WHERE employee_id = 101;If the row qualifies for the update, the applicable triggers run according to their timing. If a BEFORE trigger raises an error, the row operation does not successfully complete, and the corresponding AFTER trigger does not run for that unsuccessful row operation.
MySQL requires the FOR EACH ROW clause in trigger definitions. It means that the trigger body executes separately for each affected row.
UPDATE employees
SET department = 'IT'
WHERE employee_id = 101;If one row qualifies and is processed, the applicable UPDATE triggers execute for that row.
UPDATE employees
SET department = 'General'
WHERE salary < 50000.00;If several rows qualify, the trigger executes separately for each affected row. An AFTER UPDATE trigger that inserts an audit record for each qualifying row may therefore create several audit records from a single SQL statement.
| Feature | Row-Level Trigger | Statement-Level Trigger |
|---|---|---|
| Execution frequency | Once for each affected row. | Once for the entire SQL statement. |
| MySQL support | Supported. | Not supported as a trigger type. |
| Typical purpose | Validate or audit individual rows. | Not directly available in MySQL triggers. |
If an application needs one action per statement instead of one action per row, that behavior may need to be implemented in application logic or a stored procedure.
OLD and NEW are special references that provide access to the row values associated with the triggering operation. Their availability depends on the event.
| Trigger Event | OLD Available? | NEW Available? |
|---|---|---|
| INSERT | No | Yes |
| UPDATE | Yes | Yes |
| DELETE | Yes | No |
DELIMITER $$
CREATE TRIGGER before_salary_check
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF NEW.salary <= 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary must be greater than zero';
END IF;
END$$
DELIMITER ;In this example, NEW.salary represents the proposed salary, while OLD.salary represents the existing salary. The trigger rejects an update if the proposed salary is zero or negative.
A BEFORE trigger can assign values to eligible NEW columns before the row is stored. For example, it can normalize a value or set an update timestamp.
DELIMITER $$
CREATE TRIGGER before_employee_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
SET NEW.updated_at = CURRENT_TIMESTAMP;
END$$
DELIMITER ;This example assumes that the employees table contains an updated_at column. Assigning to NEW in an AFTER trigger has no effect because the row change has already occurred.
In a BEFORE INSERT trigger, NEW for an AUTO_INCREMENT column is initially 0 rather than the generated sequence value. Therefore, a BEFORE INSERT trigger should not assume that it can read the final automatically generated identifier.
A table can have multiple triggers associated with the same event and timing. MySQL allows the relative order of such triggers to be specified with FOLLOWS or PRECEDES.
DELIMITER $$
CREATE TRIGGER before_employee_update_a
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
SET NEW.updated_at = CURRENT_TIMESTAMP;
END$$
CREATE TRIGGER before_employee_update_b
BEFORE UPDATE ON employees
FOR EACH ROW FOLLOWS before_employee_update_a
BEGIN
IF NEW.salary <= 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary must be positive';
END IF;
END$$
DELIMITER ;FOLLOWS specifies that the second trigger executes after the named trigger among triggers with the same event and timing. PRECEDES specifies the opposite ordering relationship.
Without an explicit ordering clause, triggers with the same event and timing execute in creation order. When triggers depend on one another, make their intended order explicit and keep dependencies to a minimum.
A trigger can execute SQL statements that succeed or fail. If a trigger raises an error, the statement that activated it fails. The final effect on stored data depends on the storage engine and transaction context.
DELIMITER $$
CREATE TRIGGER before_product_price_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
IF NEW.price <= 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Product price must be greater than zero';
END IF;
END$$
DELIMITER ;This trigger rejects an update when the proposed product price is zero or negative. The application should handle the returned database error instead of assuming that every UPDATE statement succeeds.
Always test failure and rollback behavior using the storage engines and transaction settings used by your application.
A transaction is a group of database operations treated as a unit of work. When the triggering statement modifies transactional tables such as InnoDB tables, trigger operations on transactional tables generally participate in the same transaction.
START TRANSACTION;
UPDATE employees
SET salary = 52000.00
WHERE employee_id = 101;
SELECT *
FROM employee_audit
WHERE employee_id = 101;
ROLLBACK;If employees and employee_audit both use InnoDB, the salary update and any transactional audit insertion performed by the trigger are rolled back when this transaction is rolled back. The audit query executed before ROLLBACK can show the uncommitted entry within the current transaction, but that entry should not remain after the rollback.
A trigger runs because its associated table event occurs. It is not normally executed by calling its name through CALL. To activate it, perform the relevant INSERT, UPDATE, or DELETE operation.
MySQL supports row-level triggers rather than statement-level triggers. A trigger cannot be defined to execute only once for the entire UPDATE statement regardless of the number of rows affected.
A trigger cannot modify a table that is already being used by the statement that invoked the trigger in a prohibited way. For example, a trigger on employees cannot simply issue another UPDATE against employees to modify the same table as part of that triggering operation.
Use NEW values in a BEFORE trigger to adjust the current row where appropriate, or redesign the operation using another table, a stored procedure, or application logic.
A trigger can call a stored procedure, but it cannot use CALL to invoke a procedure that returns result sets to the client or uses dynamic SQL. Stored procedures can return data to the trigger through OUT or INOUT parameters where supported.
A trigger body cannot independently manage transaction boundaries using statements such as START TRANSACTION, COMMIT, or ROLLBACK. Transaction control must be managed by the surrounding application or database session. ROLLBACK TO SAVEPOINT is permitted because it does not end the transaction.
A trigger is not an interactive query interface and cannot return a result set directly to the client. If a workflow needs to return rows or values to an application, use a SELECT statement, stored procedure, or application-level operation.
Because trigger logic runs for each affected row, expensive queries and unnecessary writes can slow down large INSERT, UPDATE, and DELETE operations. Keep trigger bodies efficient and test bulk operations.
A single UPDATE statement may perform additional writes through a trigger. Developers who inspect only the application's SQL may not immediately see those side effects. Document trigger behavior and inspect trigger definitions when troubleshooting.
Triggers can depend on specific columns, tables, and permissions. Schema changes may break trigger logic or alter its behavior. Include trigger definitions in database migrations and test them alongside table changes.
OLD and NEW are available only for the relevant trigger events. OLD is unavailable in INSERT triggers, and NEW is unavailable in DELETE triggers. Assignments to NEW values are permitted in BEFORE triggers where appropriate but do not modify the row in AFTER triggers.
MySQL triggers cannot refer to generated columns through OLD or NEW. If trigger logic needs information associated with a generated column, review the table design and use the underlying source columns or another appropriate query where possible.
Creating a trigger requires the appropriate TRIGGER privilege on the associated table. Referencing OLD or NEW values and assigning to NEW columns may also require relevant privileges. A trigger can fail to be created or executed if the required privileges are missing.
| Limitation | Why It Matters | Recommended Approach |
|---|---|---|
| No direct invocation | Trigger logic runs only through table events. | Use a stored procedure or application logic for explicit orchestration. |
| Row-level execution | Logic runs for every affected row. | Optimize the trigger and test bulk operations. |
| Cannot directly return result sets | The trigger cannot act as a query response to the client. | Use a query or stored procedure. |
| Cannot independently control transaction boundaries | COMMIT and ROLLBACK are not available for independent transaction control inside a trigger. | Manage the transaction in the calling application or session. |
| Restrictions on modifying the triggering table | Conflicting modifications can be prohibited. | Use NEW in a BEFORE trigger or redesign the workflow. |
| Hidden side effects | One SQL statement may produce additional writes. | Document trigger behavior and audit dependencies. |
| Engine-dependent rollback behavior | Nontransactional tables cannot provide full transactional rollback. | Use transactional tables where atomic behavior is required. |
| Schema dependencies | Table changes may affect trigger logic. | Version-control and test triggers with migrations. |
Triggers and stored procedures both support database logic, but their invocation and intended uses differ.
| Feature | Trigger | Stored Procedure |
|---|---|---|
| Invocation | Runs automatically on a table event. | Called explicitly using CALL. |
| Typical use | Validation, auditing, and automatic field handling. | Multi-step workflows and database operations. |
| Execution frequency | Once per affected row. | Depends on how and when it is called. |
| Transaction control | Cannot independently manage transaction boundaries. | Transaction control may be possible depending on context and restrictions. |
| Return result sets | Cannot return result sets directly to the client. | Can return result sets through SELECT statements. |
| Manual execution | Activated through its associated event. | Can be invoked explicitly by authorized users or applications. |
Use triggers for automatic behavior tied to table events. Use stored procedures or application logic when a workflow needs explicit orchestration, multi-step processing, or result sets.
This example demonstrates trigger execution and transaction behavior using an employee table and a salary audit table.
CREATE TABLE employees_demo (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
salary DECIMAL(10,2) NOT NULL
) ENGINE = InnoDB;
CREATE TABLE employee_salary_audit (
audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
employee_id INT NOT NULL,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;
INSERT INTO employees_demo
(employee_id, employee_name, salary)
VALUES
(101, 'Aarav Sharma', 40000.00),
(102, 'Priya Verma', 50000.00);DELIMITER $$
CREATE TRIGGER after_employee_salary_update
AFTER UPDATE ON employees_demo
FOR EACH ROW
BEGIN
IF NOT (OLD.salary <=> NEW.salary) THEN
INSERT INTO employee_salary_audit (
employee_id,
old_salary,
new_salary,
changed_at
)
VALUES (
OLD.employee_id,
OLD.salary,
NEW.salary,
CURRENT_TIMESTAMP
);
END IF;
END$$
DELIMITER ;UPDATE employees_demo
SET salary = 45000.00
WHERE employee_id = 101;
SELECT *
FROM employee_salary_audit
WHERE employee_id = 101;The audit table should contain a row showing the old salary of 40000.00 and the new salary of 45000.00.
UPDATE employees_demo
SET salary = 45000.00
WHERE employee_id = 101;
SELECT *
FROM employee_salary_audit
WHERE employee_id = 101
ORDER BY audit_id;Because the trigger compares OLD.salary and NEW.salary, it should not insert another audit entry if the salary remains unchanged.
UPDATE employees_demo
SET salary = salary + 1000.00
WHERE employee_id IN (101, 102);
SELECT *
FROM employee_salary_audit
ORDER BY audit_id;If both employees' salaries change successfully, the trigger executes for each affected row and records each salary change.
START TRANSACTION;
UPDATE employees_demo
SET salary = salary + 500.00
WHERE employee_id = 101;
SELECT *
FROM employee_salary_audit
WHERE employee_id = 101
ORDER BY audit_id;
ROLLBACK;
SELECT *
FROM employees_demo
WHERE employee_id = 101;
SELECT *
FROM employee_salary_audit
WHERE employee_id = 101
ORDER BY audit_id;Because both tables use InnoDB, the update and audit insertion made in this transaction should be rolled back together. The final SELECT statements allow you to verify the state after rollback.
| Problem | Possible Cause | Solution |
|---|---|---|
| Trigger does not run | Wrong table, event, timing, or trigger definition. | Use SHOW TRIGGERS and inspect INFORMATION_SCHEMA.TRIGGERS. |
| Audit record is missing | The relevant value did not change, the statement failed, or the trigger is not attached as expected. | Check OLD and NEW comparisons and test the UPDATE statement. |
| Trigger raises an error | A validation rule or SQL statement failed. | Read the database error and review the trigger body. |
| Unexpected number of audit rows | The statement affected multiple rows or multiple triggers record the same event. | Check the WHERE condition and inspect all relevant triggers. |
| Rollback does not remove every change | A nontransactional table may be involved. | Check the storage engines and use transactional tables where atomicity is required. |
| Trigger creation fails | Syntax, delimiter, privilege, or name conflict. | Check the SQL definition, client delimiter, permissions, and existing trigger names. |
| Update is slower than expected | Trigger logic performs costly queries or writes for every row. | Reduce unnecessary operations and review indexes and query plans. |
| Trigger fails after a schema change | The body references a renamed or removed column or table. | Update the trigger as part of the schema migration and test it. |
SHOW TRIGGERS;SELECT
TRIGGER_NAME,
EVENT_MANIPULATION,
ACTION_TIMING,
EVENT_OBJECT_TABLE,
ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
ORDER BY EVENT_OBJECT_TABLE, TRIGGER_NAME;SHOW CREATE TRIGGER after_employee_salary_update;DROP TRIGGER IF EXISTS after_employee_salary_update;Inspect a trigger before removing it from a production database. Dropping a trigger stops its automatic execution but does not delete historical rows it previously inserted.
A trigger is a database object that executes automatically when a specified INSERT, UPDATE, or DELETE event occurs on its associated table.
No. MySQL supports row-level triggers, which execute for each affected row.
It means the trigger body runs separately for each row affected by the triggering statement.
A BEFORE trigger runs before the row operation is completed and can validate or adjust eligible NEW values. An AFTER trigger runs after the row operation succeeds and is commonly used for auditing.
They are references to row values before and after the operation, available according to the trigger event.
No. A trigger is activated through its associated table event rather than invoked like a stored procedure.
No. A trigger cannot return a result set directly to the client. Use a query, stored procedure, or application logic when results must be returned.
A trigger cannot independently control transaction boundaries using COMMIT or ROLLBACK. ROLLBACK TO SAVEPOINT is permitted because it does not end the transaction.
The triggering statement fails. Rollback behavior depends on the storage engines and transaction context; transactional operations generally roll back together when the statement or transaction is rolled back.
They execute for each affected row and may perform additional queries or writes, adding work to the original SQL statement.
MySQL restricts operations that modify a table already being used by the statement that invoked the trigger. Use eligible NEW assignments in a BEFORE trigger or redesign the operation.
Use SHOW TRIGGERS or query INFORMATION_SCHEMA.TRIGGERS.
FOLLOWS and PRECEDES specify the relative order of triggers sharing the same timing and event.
It can add overhead and be difficult to trace if undocumented. It records only the information explicitly implemented in the trigger.
A stored procedure may be more appropriate for explicitly invoked multi-step workflows, transaction orchestration, or operations that need to return result sets.
Understanding trigger execution and limitations is essential for building reliable MySQL databases. Triggers can automate validation, timestamps, and auditing, but they also introduce execution rules, performance costs, and restrictions that must be considered during database design.
Use row-level triggers when behavior must occur for each affected row, select BEFORE or AFTER according to the task, understand OLD and NEW, and test error and rollback scenarios with the storage engines used by your application. Keep trigger logic focused, documented, and tested alongside schema changes.
Explore the course: MySQL Training Course at JustAcademy
Register for a demo: JustAcademy Course Demo Registration
Visit the website: JustAcademy
